﻿--查询标记为被删除的Item
Create View if not exists RemovedAttachments
AS
SELECT [itemData].[itemID]
	,[items].[key]
	,[value]
	,[dateDeleted]
FROM [itemData]
	,[itemDataValues]
	,[deletedItems]
	,[items]
where [itemData].valueID = [itemDataValues].valueID
	and [itemData].itemID = [deletedItems].itemID
	and [itemData].itemID = [items].itemID
	and (
		[value] like '%.pdf'
		or [value] like '%.docx'
		or [value] like '%.doc'
		or [value] like '%.txt'
		or [value] like '%.md'
		or [value] like '%.caj'
		)
go
select * from RemovedAttachments;
 
 
------------------------------------------------
-----查询所有附件
Create View if not exists AllAttachments
AS
SELECT [items].[itemID]
	,[key]
	,[itemTypes].typeName
	,[value]
FROM [items]
	,[itemTypes]
	,[itemData]
	,[itemDataValues]
where [items].itemTypeID = [itemTypes].itemTypeID
	and [itemData].itemID = [items].itemID
	and [itemData].valueID = [itemDataValues].valueID
	and [itemTypes].typeName = 'attachment'
	and (
		[value] like '%.pdf'
		or [value] like '%.docx'
		or [value] like '%.doc'
		or [value] like '%.txt'
		or [value] like '%.md'
		or [value] like '%.caj'
		)
go

---应该被保留下来的附件
--drop view ShouldKeepedAttachments;
Create View if not exists ShouldKeepedAttachments
AS
select [itemID]
	,[key]
	,[typeName]
	,[value]
from AllAttachments
where [key] not in (
		select [key]
		from RemovedAttachments
		)
go

select * from AllAttachments where value = 'Girshick_2015_Fast R-CNN.pdf';
select * from RemovedAttachments where value = 'Girshick_2015_Fast R-CNN.pdf';
select * from ShouldKeepedAttachments where value = 'Girshick_2015_Fast R-CNN.pdf';


-- 查询目录树
SELECT [collections].collectionID
	,[collections].[collectionName]
	,[itemDataValues].value
	,[collections].[parentCollectionID]
	,[collections].[clientDateModified]
	,[collections].[key]
FROM [collections]
	,[collectionItems]
	,[itemData]
	,[itemDataValues]
where [collectionItems].collectionID = [collections].collectionID
	and [itemData].itemID = [collectionItems].itemID
	and [itemData].valueID = [itemDataValues].valueID
go